package zy.testsqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;

import zy.testsqlite.Database.DatabaseHelper;

public class MainActivity extends AppCompatActivity {
    private DatabaseHelper dbHelper;
    int i;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = new DatabaseHelper(this,"BookStore.db",null,1);
        Button createDatabase = (Button)findViewById(R.id.create_database);
        createDatabase.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
            //创建或打开现有的数据库
                dbHelper.getWritableDatabase();
            }
        });
        Button addData = (Button) findViewById(R.id.add_data);
        addData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                //getWritableDatabase()会返回一个SQLiteDatabase对象
                try {
                        SQLiteDatabase db = dbHelper.getWritableDatabase();
                        ContentValues values = new ContentValues();
                        values.put("name","The Da Vinci Code");
                        values.put("price",20);
                        values.put("author","Scott");
                        //insert（）方法中第一个参数是表名，第二个参数是表示给表中未指定数据的自动赋值为NULL。第三个参数是一个ContentValues对象
                        long l=db.insert("Book",null,values);
//                        values.clear();
//
//                        values.put("name","The Lost Symbol");
//                        values.put("author","Scott");
//                        values.put("price",30);
//                        values.put("pages",600);
//                        long l=db.insert("Book", null, values);



                        values.clear();
                        values.put("id","c42f7de28405c6a320170406145449880");
                        l=db.insert("groups", null, values);

                        values.clear();
                        values.put("name","王好");
                        values.put("groupId","c42f7de28405c6a320170406145449880");
                        l=db.insert("user", null, values);

                        Log.i("hello","SQLiteException"+l);
                        }catch (RuntimeException e){
                            Log.e("hello","SQLiteException"+e.toString());
                }
            }
        });
        Button updateData = (Button) findViewById(R.id.update_data);
        updateData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("price",19.99);
                //仔细update中提示的参数（String table,ContentValues,String whereClause,String[] whereArgs）
                //第三滴四行指定具体更新那几行。注意第三个参数中的？是一个占位符，通过第四个参数为第三个参数中占位符指定相应的内容。
                db.update("Book",values,"name=?",new String[]{"The Da Vinci Code"});
            }
        });

        Button deleteData = (Button) findViewById(R.id.delete_data);
        deleteData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
//                ContentValues values = new ContentValues();
//                values.put("price",19.99);
//                //仔细update中提示的参数（String table,ContentValues,String whereClause,String[] whereArgs）
//                //第三滴四行指定具体更新那几行。注意第三个参数中的？是一个占位符，通过第四个参数为第三个参数中占位符指定相应的内容。
//                db.update("Book",values,"name=?",new String[]{"The Da Vinci Code"});
               /// db.delete("Book","name=?",new String[] {"The Da Vinci Code"});
                int re=db.delete("groups","id=?",new String[] {"c42f7de28405c6a320170406145449880"});
                Log.i("hello","re="+re);
            }
        });


        Button queryButton = (Button) findViewById(R.id.query_data);
        queryButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                //指明去查询Book表。
                Cursor cursor = db.query("Book",null,null,null,null,null,null);
                //调用moveToFirst()将数据指针移动到第一行的位置。
                if (cursor.moveToFirst()){
                    do {
                        //然后通过Cursor的getColumnIndex()获取某一列中所对应的位置的索引
                        String name = cursor.getString(cursor.getColumnIndex("name"));
                        String author = cursor.getString(cursor.getColumnIndex("author"));
                        double price = cursor.getDouble(cursor.getColumnIndex("price"));
                        Log.i("hello","book name is "+name);
                        Log.i("hello","book author is "+author);
                        Log.i("hello","book price is "+price);
                    }while(cursor.moveToNext());
                }
                cursor.close();
                cursor = db.query("groups",null,null,null,null,null,null);
                if (cursor.moveToFirst()){
                    do {
                        //然后通过Cursor的getColumnIndex()获取某一列中所对应的位置的索引
                        String id= cursor.getString(cursor.getColumnIndex("id"));
                        Log.i("hello","groups id is "+id);
                    }while(cursor.moveToNext());
                }
                cursor.close();
                cursor = db.query("user",null,null,null,null,null,null);
                if (cursor.moveToFirst()){
                    do {
                        //然后通过Cursor的getColumnIndex()获取某一列中所对应的位置的索引
                        String name= cursor.getString(cursor.getColumnIndex("name"));
                        String  groupId= cursor.getString(cursor.getColumnIndex("groupId"));
                        Log.i("hello","user name is "+name);
                        Log.i("hello","user groupId  is "+groupId);
                    }while(cursor.moveToNext());
                }
            }
        });



    }
}
